package com.ymttest.database.annotation.product;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ymttest.database.annotation.SqlMapperFactory;

public interface IntergratedProductMapper {
	
	@Select("select * from ymt_products where sproductid = #{productId}")
	@Options(flushCache = true)  
	public Map<String,Object> selectProductByProdId(@Param("productId")String productId);
	
	
	@Select("select * from ymt_catalogs where sproductid = #{productId}")
	public List<Map<String,Object>> selectCatalogsByProdId(@Param("productId")String productId);
	
	@Select("select * from ymt_catalogs where scatalogId = #{catalogId}")
	public Map<String,Object> selectCatalogsByCatalogId(@Param("catalogId")String catalogId);
	
	
	@Select("select * from Ymt_ProductPicture where sproductid = #{productId} and iaction>-1" )
	public List<Map<String,Object>> selectPictureByProdIdAndAction0(@Param("productId")String productId);
	
	
	@Select("select * from Ymt_ProductDescConfigWord where ProductID = #{productId} and UserID = #{sellerId}" )
	public List<Map<String,Object>> selectDescConfigWordByProdIdAndSellerId(@Param("productId")String productId,@Param("sellerId")Integer sellerId);
	
	
	@Select("select * from Ymt_ProductDescConfigPicture where ProductID = #{productId} and UserID = #{sellerId} and iaction=0" )
	public List<Map<String,Object>> selectDescConfigPictureByProdIdAndSellerId(@Param("productId")String productId,@Param("sellerId")Integer sellerId);
	
	
	@Select("select * from Ymt_ProductDescMap where productid = #{productId}")
	public List<Map<String,Object>> selectDescMapPByProdId(@Param("productId")String productId);
	
	
	@Select("select * from Ymt_ProductVideo where productid = #{productId} and sellerId = #{sellerId} and action=0")
	public List<Map<String,Object>> selectProdVideoByProdId(@Param("productId")String productId,@Param("sellerId")Integer sellerId);
	
	
	@Select("select * from Ymt_ProductProperty where productid = #{productId} and status = 0")
	public List<Map<String,Object>> selectPropertyByProdId(@Param("productId")String productId);
	
	
	@Select("select * from Ymt_ProductDeliveryTemplate where productid = #{productId} and templateId = #{tplId} and status = 1")
	public List<Map<String,Object>> selectDeliveryTplByProductIdAndTplId(@Param("productId")String productId,@Param("tplId")Integer tplId);
	
	
	@Select("select * from Ymt_SubCatalogs where catalogId = #{catalogId}")
	public List<Map<String,Object>> selectSubCatalogByCatalogId(@Param("catalogId")String catalogId);
	
	
	@Select("select a.sProductProperty,b.sCategoryAttribute,b.sPicUrl ,a.sProductId from Ymt_CatalogProperty as a left join Ymt_CatalogSetAttributes as b on a.sCatalogPropertyId = b.sCatalogPropertyId  where a.sCatalogId =#{catalogId}")
	public List<Map<String,Object>> selectCatalogProAndValByCatalogId(@Param("catalogId")String catalogId);
	
	
	@Select("select * from Ymt_SubCatalogs where SubCatalogId =#{subCatalogId}")
	public List<Map<String,Object>> selectSubCatalogBySubCatalogId(@Param("subCatalogId") int subCatalogId);
	
	
	@Select("SELECT COUNT (DISTINCT p.sProductId) as total FROM Ymt_Products p INNER JOIN Ymt_Catalogs c  ON p.sProductId = c.sProductId WHERE p.iAction >- 1 AND c.iAction !=- 1 AND c.isPreSale = 1 AND p.iUserId = #{userId}")
	public Map<String,Object> selectUserPreSaleCount(@Param("userId") int userId);
	
	
	@Select("select * from Spu_CategoryProperty a left join Spu_Property b on a.PropertyId = b.PropertyId where a.ThirdCategoryId = #{thirdCategoryId}")
	public List<Map<String,Object>> selectSpuPropertyByThirdCategoryId(@Param("thirdCategoryId") int thirdCategoryId);
	
	
	@Select("select * from Spu_PropertyValue where PropertyId = #{propertyId}")
	public List<Map<String,Object>> selectSpuPropertyValByPropId(@Param("propertyId") int propertyId);
	
	@Select("select * from Spu_Product where SpuId = #{spuId}")
	public Map<String,Object> selectSpuProductBySpuId(@Param("spuId") int spuId);
	
	
	@Select("select * from Ymt_ProductsInActivity where sProductId = #{productId}")
	public List<Map<String,Object>> selectActivityProdByProdId(@Param("productId") String productId);
	
	
	@Select("select * from Ymt_Activity where iActivityId = #{activityId}")
	public Map<String,Object> selectActivityByActivityId(@Param("activityId") int activityId);

	
	@Select("select * from Ymt_productsInLive  where  iActivityId =#{liveId}")
	public List<Map<String,Object>> selectLiveProdLiveId(@Param("liveId") int liveId);
	
	
	@Select("select * from Ymt_productsInLive where sProductId = #{productId} and iActivityId = #{liveId}")
	public List<Map<String,Object>> selectLiveProdByProdIdAndLiveId(@Param("productId")String productId,@Param("liveId")Integer liveId);
	
	@Select("SELECT iSellStatus,case when a.StockNum>0 then 1 else 0 end as 'IsSoldout',COUNT(1) as 'DataCount' FROM "
			+ "( SELECT i.iSellStatus,i.sProductId,SUM(c.iNum) as StockNum FROM Ymt_ProductsInLive i JOIN Ymt_Catalogs c  ON c.sProductId=i.sProductId  AND c.iAction>-1 "
			+ "WHERE i.iActivityId=#{liveId} AND i.iUserId= #{userId} AND i.iAction>-1 group by i.iSellStatus,i.sProductId) "
			+ "AS a group by iSellStatus,case when a.StockNum>0 then 1 else 0 end")
	@Options(flushCache = true)  
	public List<Map<String,Object>> selectLiveProductTabCount(@Param("userId")int userId,@Param("liveId")Integer liveId);
	
	
	
	
	@Select("select * from Ymt_ProductBrand")
	public List<Map<String,Object>> selectAllBrands();
	
	@Select("select * from Ymt_ProductBrand where sBrandEn = #{brandEn}")
	public List<Map<String,Object>> selectBrandsByBrandEn(@Param("brandEn")String brandEn);
	
	@Select("select * from Ymt_ProductBrand where iBrandId = #{brandId}")
	public Map<String,Object> selectBrandByBrandId(@Param("brandId")int brandId);
	
	
	
	@Select("select * from Ymt_SellerBrandApply where EnName = #{brandEn}")
	@Options(flushCache = true)  
	public List<Map<String,Object>> selectApplyBrandsByBrandEn(@Param("brandEn")String brandEn);
	
	
	
	
	@Select("select * from Ymt_ProductBrand where dUpdateTime>#{selectDate}")
	public List<Map<String,Object>> selectAllBrandsAfterSelectDate(@Param("selectDate")String selectDate);
	
	@Select("SELECT distinct cp.sProductProperty FROM Ymt_Products p INNER LOOP JOIN Ymt_Catalogs c  ON p.sProductId = c.sProductId "
			+ "INNER LOOP JOIN Ymt_CatalogProperty cp ON c.sCatalogId = cp.sCatalogId WHERE  p.iUserId=#{sellerId}")
	public List<Map<String,Object>> selectSpecificationsByUserId(@Param("sellerId")int sellerId);
	
	
	
	
	@Update("update YMT_SellerLoginLimitAutoOnShevel set ProcessStatus='D', StartTime='2015-11-24 16:07:34.943' where userId = #{sellerId}")
	public int updateSellerLoginLimitAutoOnShevelAsPrevious(@Param("sellerId")int sellerId);
	
	
	@Update("update Ymt_Products set IsPspProduct = #{psp} where sProductId =#{productId}")
	public int updatePspByProductId(@Param("productId")String productId,@Param("psp")boolean psp);
	
	
	@Update("UPDATE dbo.Ymt_Products SET validEnd = GETDATE() - 1 WHERE iUserId = #{sellerId} ")
	public int updateProductoffSale(@Param("sellerId")int sellerId);
	
	
	@Insert("insert into  Ymt_ProductPspInfo values (#{sellerId},#{productId},1,GETDATE(),GETDATE())")
	public int insertProductInfo(@Param("productId")String productId,@Param("sellerId")int sellerId);
	
	
	@Delete("delete from Ymt_ProductDeliveryTemplate where SellerId = #{sellerId} and TemplateId = #{templateId}")
	public int deleteDeliveryTemplateBySellerIdAndTemplateId(@Param("sellerId")int sellerId,@Param("templateId")int templateId);
	
	
	
	
	
	
	
	
	public static void main(String args[]){
		SqlMapperFactory.getIntergratedProductMapper().insertProductInfo("0000332c-c165-4245-b044-c6627eab37ee", 444);
	}
}






